package SnpDbHandling;

import SnpDbEntities.Category;
import SnpDbEntities.User;
import SnpDbHndInterfaces.DBCatHandling;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.logging.Level;

/**
 *
 * @author Łukasz 'byku' Guzik
 */
public class DBCatHandler extends java.rmi.server.UnicastRemoteObject implements
	DBCatHandling
{
    private final Connection dbCon;
    private final User connectedUser;

    public DBCatHandler(Connection dbCon, User connectedUser) throws
	    RemoteException
    {
	this.dbCon = dbCon;
	this.connectedUser = connectedUser;
    }

    @Override
    public Category getPublicCategory(int id) throws RemoteException
    {
	Category result = null;
	try
	{
	    result = getCategory(true, id);
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    result = null;
	} finally
	{
	    return result;
	}
    }

    @Override
    public LinkedHashMap<Integer, Category> getAllPublicCategories() throws
	    RemoteException
    {
	LinkedHashMap<Integer, Category> result = null;
	try
	{
	    result = getAllCategories(true);
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    result = null;
	} finally
	{
	    return result;
	}
    }

    @Override
    public int addPublicCategory(Category newCategory) throws RemoteException
    {
	int result = -1;

	try
	{
	    result = addCategory(true, newCategory);
	} catch(Exception e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return -1;
	}
	return result;
    }

    @Override
    public boolean deletePublicCategory(int id) throws RemoteException
    {
	return deleteCategory(true, id);
    }

    @Override
    public boolean updatePublicCategory(Category newCategoryData) throws
	    RemoteException
    {
	return updateCategory(true, newCategoryData);
    }

    @Override
    public ArrayList<Integer> getCategoryModerators(int categoryID) throws
	    RemoteException
    {
	ArrayList<Integer> result = new ArrayList<Integer>();

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	    String query = "SELECT ModeratorID FROM ModeratorPublicCategory WHERE PublicCategoryID = " + categoryID + ";";
	    ResultSet rs = stmt.executeQuery(query);

	    while(rs.next())
	    {
		result.add(rs.getInt(1));
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return result;
	}
    }

    @Override
    public boolean addModeratorToCategory(int moderatorID, int categoryID)
	    throws RemoteException
    {
	if(!connectedUser.canChangePublicCategories())
	{
	    return false;
	}

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "INSERT INTO ModeratorPublicCategory (ModeratorID, PublicCategoryID) "
		    + "VALUES (" + moderatorID + ", " + categoryID + ");";

	    stmt.executeUpdate(query);
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return false;
	}

	return true;
    }

    @Override
    public boolean removeModeratorFromCategory(int moderatorID, int categoryID)
	    throws RemoteException
    {
	if(!connectedUser.canChangePublicCategories())
	{
	    return false;
	}
	
	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "DELETE FROM ModeratorPublicCategory WHERE (ModeratorID = " + moderatorID + ")AND(PublicCategoryID = " + categoryID + ");";
	    stmt.executeUpdate(query);
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return false;
	}
	return true;
    }

    @Override
    public Category getPrivateCategory(int id) throws RemoteException
    {
	Category result = null;
	try
	{
	    result = getCategory(false, id);
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    result = null;
	} finally
	{
	    return result;
	}
    }

    @Override
    public LinkedHashMap<Integer, Category> getAllPrivateCategories() throws
	    RemoteException
    {
	LinkedHashMap<Integer, Category> result = null;
	try
	{
	    result = getAllCategories(false);
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    result = null;
	} finally
	{
	    return result;
	}
    }

    @Override
    public int addPrivateCategory(Category newCategory) throws RemoteException
    {
	int result = -1;

	try
	{
	    result = addCategory(false, newCategory);
	} catch(Exception e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return -1;
	}
	return result;
    }

    @Override
    public boolean deletePrivateCategory(int id) throws RemoteException
    {
	return deleteCategory(false, id);
    }

    @Override
    public boolean updatePrivateCategory(Category newCategoryData) throws
	    RemoteException
    {
	return updateCategory(false, newCategoryData);
    }

    private Category getCategory(boolean isPublic, int id) throws SQLException
    {
	Statement stmt = null;
	CategoryRecord retCatRec = null;

	String query = "SELECT * FROM ";
	if(isPublic)
	{
	    query += "PublicCategories WHERE ID = " + id + ";";
	} else
	{
	    query += "PrivateCategories WHERE (ID = " + id + ")AND(OwnerID = " + connectedUser.getId() + ");";
	}

	try
	{
	    stmt = dbCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next())
	    {
		retCatRec = CategoryRecord.getFromResultSet(rs);
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return null;
	}
	return retCatRec != null ? retCatRec.getCategory() : null;
    }

    private LinkedHashMap<Integer, Category> getAllCategories(boolean isPublic)
	    throws SQLException
    {
	Statement stmt = null;
	CategoryRecord tempCatRec = null;
	LinkedHashMap<Integer, Category> resultHashMap = new LinkedHashMap<Integer, Category>();

	String query = "SELECT * FROM ";
	if(isPublic)
	{
	    query += "PublicCategories;";
	} else
	{
	    query += "PrivateCategories WHERE OwnerID = " + connectedUser.getId() + ";";
	}

	try
	{
	    stmt = dbCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	    ResultSet rs = stmt.executeQuery(query);
	    while(rs.next())
	    {
		tempCatRec = CategoryRecord.getFromResultSet(rs);
		resultHashMap.put(tempCatRec.getCategory().getId(), tempCatRec.getCategory());
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return resultHashMap;
	}
    }

    private int addCategory(boolean isPublic, Category newCategory) throws
	    SQLException
    {
	int result = -1;
	CategoryRecord newCategoryRecord = new CategoryRecord(newCategory);
	if(isPublic && !connectedUser.canChangePublicCategories())
	    return -1;

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "SELECT * FROM ";
	    if(isPublic)
	    {
		query += "PublicCategories WHERE ID = -1;";
	    } else
	    {
		query += "PrivateCategories WHERE ID = -1;";
	    }

	    ResultSet rs = stmt.executeQuery(query);
	    rs.moveToInsertRow();
	    newCategoryRecord.insertIntoResultSet(rs);
	    if(!isPublic)
	    {
		rs.updateInt("OwnerID", connectedUser.getId());
	    }
	    rs.insertRow();
	    rs.first();
	    result = rs.getInt("ID");
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return -1;
	}
	return result;
    }

    private boolean deleteCategory(boolean isPublic, int id)
    {
	if(isPublic && !connectedUser.canChangePublicCategories())
	    return false;

	try
	{
	    dbCon.setAutoCommit(false);
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String deleteQuery = "DELETE FROM ";
	    String updateQuery = "UPDATE ";
	    if(isPublic)
	    {
		deleteQuery += "PublicCategories WHERE ID = " + id + ";";
		updateQuery += "PublicCategories SET ParentID=(SELECT ParentID FROM PublicCategories "
			+ "WHERE ID = " + id + ") WHERE ParentID = " + id + ";";
	    } else
	    {
		deleteQuery += "PrivateCategories WHERE (ID = " + id + ")AND(OwnerID = " + connectedUser.getId() + ");";
		updateQuery += "PrivateCategories SET ParentID=(SELECT ParentID FROM PrivateCategories "
			+ "WHERE ID = " + id + ") WHERE ParentID = " + id + ";";
	    }

	    
	    stmt.executeUpdate(updateQuery);
	    if(stmt.executeUpdate(deleteQuery) != 1)
	    {
		dbCon.rollback();
	    } else
	    {
		dbCon.commit();
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    try
	    {
		dbCon.rollback();
	    } catch(SQLException rollbackE)
	    {
		DBHndLogger.logException(Level.SEVERE, rollbackE);
	    }
	    DBHndLogger.logException(Level.WARNING, e);
	    return false;
	} finally
	{
	    try
	    {
		dbCon.setAutoCommit(true);
	    } catch(SQLException e)
	    {
		DBHndLogger.logException(Level.SEVERE, e);
	    }
	}
	return true;
    }

    private boolean updateCategory(boolean isPublic, Category newCatData)
    {
	CategoryRecord newCatDataRec = new CategoryRecord(newCatData);

	if(isPublic && !connectedUser.canChangePublicCategories())
	    return false;

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "SELECT * FROM ";

	    if(isPublic)
	    {
		query += "PublicCategories WHERE ID = " + newCatData.getId() + ";";
	    } else
	    {
		query += "PrivateCategories WHERE (ID = " + newCatData.getId() + ")AND(OwnerID = " + connectedUser.getId() + ");";
	    }

	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next())
	    {
		newCatDataRec.insertIntoResultSet(rs);
		rs.updateRow();
		stmt.close();
	    } else
	    {
		stmt.close();
		return false;
	    }
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	    return false;
	}

	return true;
    }
}
